-- =============================================
-- Procedure: GetAllInterestForMonthAddon
-- Purpose:	This procedure computes the interest (straight line) on the loan db for the month supplied in the paramater @report_date.
--			The interest is calculated based  on the interest rate supplied This procedure serves as a datasouce for the report
-- Author: Garret Stephenson
-- Created: 2013-5-10
-- Modification History
-- Modified By	Modification Date	Reason
-- =====================================================================================
--  Dale McFarlane    2014-1-29    Factor in early closed data of loans
-- =====================================================================================

--exec [GetAllInterestForMonthAddon] '2013-10-05', 1
--select * from loan_addon

IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[GetAllInterestForMonthAddon]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	DROP PROCEDURE dbo.[GetAllInterestForMonthAddon]
END
GO

CREATE PROCEDURE [dbo].[GetAllInterestForMonthAddon]
(
	@report_date DATETIME,
	@interest DECIMAL(18,2)
)
AS
BEGIN
	DECLARE @month INT, @year INT
	SET @month = MONTH(@report_date)
	SET @year = YEAR(@report_date)	

	
	SELECT * FROM
	(
		SELECT 
			L.term, 
			SUM(dbo.CalInterestAddon(@month, @year, L.interest_rate, L.start_date, L.closed_date, L.end_date, L.principal, l.period)) as Interest,
			L.product,
			L.interest_rate
		FROM 
			loan_addon L
		WHERE 
			(
				(YEAR(L.end_date) = @year AND MONTH(L.end_date) >= @month) 
				OR YEAR(L.end_date) > @year) AND ((YEAR(L.start_date) = @year 
				AND MONTH(L.start_date) <= @month) OR YEAR(L.start_date) < @year
			)
		GROUP BY 
			L.product, L.term, L.interest_rate
	) AS proj_interest
	WHERE
		proj_interest.Interest> 0
	ORDER BY 
		proj_interest.term		
	
END
GO
